﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using static OfficeOpenXml.ExcelWorksheet;

namespace WAU.Utils.ExcelEpplusUtils
{
    /// <summary>
    /// 导入 Excel 结果
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class ExcelImportResult<T>
    {
        public List<T> DataList { get; set; }
        public string Msg { get; set; }
    }

    /// <summary>
    /// 导入 Excel 工具类
    /// </summary>
    public class ImportExcelUtil
    {
        /// <summary>
        /// Excel 导入时的字典项（与 ImportExcelColumnAttribute 中的属性对应）
        /// </summary>
        private class ExcelImportInfoItem
        {
            public PropertyInfo PropertyInfo { get; set; }
            public string ColumnName { get; set; }
            public bool IsRequire { get; set; }

            /// <summary>
            /// 生成导入信息序列
            /// </summary>
            /// <param name="dataType"></param>
            /// <returns></returns>
            public static ExcelImportInfoItem[] GenerateInfoArray(Type dataType)
            {
                PropertyInfo[] propertyInfos = dataType.GetProperties();
                ExcelImportInfoItem[] resultArray = propertyInfos.Select(a =>
                {
                    var attribute = a.GetCustomAttribute<ImportExcelColumnAttribute>();

                    if (attribute == null)
                    {
                        return null;
                    }

                    return new ExcelImportInfoItem
                    {
                        PropertyInfo = a,
                        ColumnName = attribute.ColumnName,
                        IsRequire = attribute.IsRequire
                    };
                }).Where(info => info != null).ToArray();

                return resultArray;
            }
        }

        /// <summary>
        /// 将 Excel 中的数据转换为 List 类型
        /// </summary>
        /// <typeparam name="T">必须无参构造函数的类型</typeparam>
        /// <param name="excelFilePath"></param>
        /// <param name="titleStartRowNumber"></param>
        /// <param name="dataStartRowNumber"></param>
        /// <param name="convertValueMethod"></param>
        /// <param name="sheetNum"></param>
        /// <returns></returns>
        public static ExcelImportResult<T> Import<T>(string excelFilePath, int titleStartRowNumber = 1, int dataStartRowNumber = 2,
            Func<PropertyInfo, object, string, object> convertValueFunc = null, int sheetNum = 0)
            where T : new()
        {
            List<T> resultList = new List<T>();

            ExcelImportInfoItem[] infoItems = ExcelImportInfoItem.GenerateInfoArray(typeof(T));

            using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFilePath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetNum];

                HandleMergedColumns(worksheet); // 处理下合并的单元格

                List<string> titles = new List<string>();
                int rowCount = worksheet.Dimension.Rows;    // 一共多少行
                int colCount = worksheet.Dimension.Columns; // 一共多少列

                // 读取所有的标题
                for (int colIndex = 1; colIndex <= colCount; colIndex++)
                {
                    titles.Add(worksheet.Cells[titleStartRowNumber, colIndex].Value.ToString());
                }

                // 内连接以 去除 Excel 标题 和 导入信息 中的无关部分，并按 Excel 标题排序
                infoItems = titles.Join(infoItems, t => t, ii => ii.ColumnName, (t, ii) => ii).ToArray();

                // 逐行读取数据
                for (int rowIndex = dataStartRowNumber; rowIndex <= rowCount; rowIndex++)
                {
                    T obj = new T();

                    // 如果整行数据都为空，则跳过
                    bool isThisRowEmpty = true;
                    for (var colIndex = 0; colIndex < colCount; colIndex++)
                    {
                        if (worksheet.Cells[rowIndex, colIndex + 1].Value != null)
                        {
                            isThisRowEmpty = false;
                            break;
                        }
                    }
                    if (isThisRowEmpty)
                    {
                        continue;
                    }

                    // 读取数据
                    for (int infoIndex = 0; infoIndex < infoItems.Length; infoIndex++)
                    {
                        PropertyInfo propertyInfo = infoItems[infoIndex].PropertyInfo;

                        int titleIndex = titles.IndexOf(infoItems[infoIndex].ColumnName);
                        object cellValue = worksheet.Cells[rowIndex, titleIndex + 1].Value;

                        if (cellValue == null)
                        {
                            if (infoItems[infoIndex].IsRequire)
                            {
                                return new ExcelImportResult<T> { Msg = $"第 { rowIndex } 行的 { infoItems[infoIndex].ColumnName } 字段不能为空" };
                            }
                            else
                            {
                                continue;
                            }
                        }

                        string value = cellValue.ToString();
                        if (propertyInfo.PropertyType == typeof(string))
                        {
                            propertyInfo.SetValue(obj, value);
                        }
                        else
                        {
                            object tmpValue;
                            if (convertValueFunc == null)
                            {
                                tmpValue = BasicConvertValueTypeMethod(propertyInfo, obj, value);
                            }
                            else
                            {
                                tmpValue = BasicConvertValueTypeMethod(propertyInfo, obj, value);
                                if (tmpValue == null)
                                {
                                    tmpValue = convertValueFunc(propertyInfo, obj, value);
                                }
                            }

                            propertyInfo.SetValue(obj, tmpValue);
                        }
                    }

                    resultList.Add(obj);
                }
            }

            return new ExcelImportResult<T> { DataList = resultList };
        }

        /// <summary>
        /// 单元格地址（如 A2，Z8） 正则
        /// </summary>
        private static Regex CellAddressRegex = new Regex("([a-zA-Z]+)(\\d+)");

        /// <summary>
        /// 处理合并的区域，给这个区域中的所有单元格赋同一个值
        /// </summary>
        /// <param name="worksheet"></param>
        private static void HandleMergedColumns(ExcelWorksheet worksheet)
        {
            MergeCellsCollection mergeCells = worksheet.MergedCells;

            foreach (var mergeCell in mergeCells)
            {
                string[] tmpFromTo = mergeCell.Split(":");
                string from = tmpFromTo[0], to = tmpFromTo[1];

                Match fromMatch = CellAddressRegex.Match(from);
                int fromRowNum = int.Parse(fromMatch.Groups[2].Value);
                int fromColNum = ConvertLetterAddressToNumber(fromMatch.Groups[1].Value);

                Match toMatch = CellAddressRegex.Match(to);
                int toRowNum = int.Parse(toMatch.Groups[2].Value);
                int toColNum = ConvertLetterAddressToNumber(toMatch.Groups[1].Value);

                string value = worksheet.Cells[fromRowNum, fromColNum].Value?.ToString();

                for (int i = fromRowNum; i <= toRowNum; i++)
                {
                    for (int j = fromColNum; j <= toColNum; j++)
                    {
                        worksheet.Cells[i, j].Value = value;
                    }
                }
            }
        }

        /// <summary>
        /// 将字母地址转为数字地址
        /// </summary>
        /// <param name="letterAddress"></param>
        /// <returns></returns>
        public static int ConvertLetterAddressToNumber(string letterAddress)
        {
            double result = 0;

            // 相当于将 二十六进制 转为 十进制
            string tmp = new string(letterAddress.ToUpper().Reverse().ToArray());
            for (var i = 0; i < tmp.Length; i++)
            {
                result += (tmp[i] - 65 + 1) * Math.Pow(26, i);
            }
            return (int)result;
        }

        /// <summary>
        /// 基本的 字符串类型值 转换为 目标类型值 的方法（尝试调用 Parse）
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="obj"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object BasicConvertValueTypeMethod(PropertyInfo propertyInfo, Object obj, string value)
        {
            Type propertyType = propertyInfo.PropertyType;
            if (propertyType.IsGenericType
                && propertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                propertyType = propertyType.GenericTypeArguments[0];
            }
            MethodInfo mi = propertyType.GetMethod("Parse", new Type[] { typeof(string) });
            if (mi != null)
            {
                return mi.Invoke(obj, new object[] { value });
            }
            else
            {
                throw new Exception("目标类型中不包含 Parse 方法，请传入 convertValueFunc 参数以使用合适的方法转换值");
            }
        }
    }

    /// <summary>
    /// 导入 Excel 时属性的的 Attribute
    /// </summary>
    [AttributeUsage(AttributeTargets.Property)]
    public class ImportExcelColumnAttribute : Attribute
    {
        public string ColumnName { get; set; }
        public bool IsRequire { get; set; }

        /// <summary>
        /// ctor
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="isRequire"></param>
        public ImportExcelColumnAttribute(string columnName, bool isRequire = true)
        {
            this.ColumnName = columnName;
            this.IsRequire = isRequire;
        }
    }
}
